#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto

if [[ $1 == "" ]];then
   db=zx_dwb
else
   db=$1
fi

${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
DELETE FROM ${db}.dwb_customer_relationship_detail;
--客户意向宽表
INSERT INTO ${db}.dwb_customer_relationship_detail
SELECT
    -- 原来的字段
    cr.id,
    SUBSTRING(cr.create_date_time, 1, 13) AS create_date_time,
    cr.update_date_time,
    cr.deleted,
    cr.origin_type,
    cr.customer_id,
    cr.itcast_school_id,
    cr.itcast_subject_id,
    cr.creator,
    cr.payment_state,
    cr.payment_time,

    -- 从客户信息表关联得到的字段
    c.area,

    -- 从客户线索表关联得到的字段
    cc.clue_state,

    -- 从员工信息表关联得到字段
    e.tdepart_id,

    -- 从员工部门表关联得到字段
    d.name                                AS tdepart_name,

    -- 从学科信息表关联得到字段
    s.name                                AS itcast_subject_name,
    -- 从校区信息表关联得到字段
    sc.name                               AS itcast_school_name,
    -- 从线索申述信息表关联得到字段
    a.customer_relationship_first_id,
    a.appeal_status,
    SUBSTRING(cr.create_date_time, 1, 7)  AS dt
FROM (SELECT * FROM zx_dwd.fact_customer_relationship WHERE end_date = '9999-99-99') cr
         LEFT JOIN zx_dwd.dim_customer c ON cr.customer_id = c.id AND c.end_date = '9999-99-99'
         LEFT JOIN zx_dwd.fact_customer_clue cc ON cr.id = cc.customer_relationship_id AND cc.end_date = '9999-99-99'
         LEFT JOIN zx_dwd.dim_employee e ON cr.creator = e.id AND e.end_date = '9999-99-99'
         LEFT JOIN zx_dwd.dim_scrm_department d ON e.tdepart_id = d.id AND d.end_date = '9999-99-99'
         LEFT JOIN zx_dwd.dim_itcast_subject s ON cr.itcast_subject_id = s.id AND s.end_date = '9999-99-99'
         LEFT JOIN zx_dwd.dim_itcast_school sc ON cr.itcast_school_id = sc.id AND sc.end_date = '9999-99-99'
         LEFT JOIN zx_dwd.dim_customer_appeal a
                   ON cr.id = a.customer_relationship_first_id AND a.end_date = '9999-99-99';

"